﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FirstTry.Model;
using System.Data.Sql;
using System.Data.SqlClient;
using FirstTry.DB;

namespace FirstTry.DB
{
    public class DBLocation
    {

        DBConnection dbc = new DBConnection();

        public List<Location> GetCities()
        {
            List<Location> cityList = new List<Location>();
            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, City FROM Location";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Location l = new Location();
                l.ID = Convert.ToInt32(dr["ID"]);
                l.city = dr["City"].ToString();


                cityList.Add(l);
            }
            return cityList;

        }

        public Location GetCityByID(string ID)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, City FROM Location WHERE ID = " + ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            Location l = new Location();
            while (dr.Read())
            {
                l.ID = Convert.ToInt32(dr["ID"]);
                l.city = dr["City"].ToString();

            }
            return l;

        }

        public void insertLocation(Location l)
        {

            String query = "INSERT INTO Location (City) VALUES (@City)";
            SqlConnection con = dbc.GetConnection();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@City", l.city);

            try
            {


                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }


        }

        public void updateLocation(string city, int ID)
        {
            SqlConnection con = dbc.GetConnection();
            String updateSql = "UPDATE Location " + "SET City = @City " + "WHERE ID = @cityID";
            SqlCommand updateCmd = new SqlCommand(updateSql, con);


            updateCmd.Parameters.AddWithValue("@City", city);
            updateCmd.Parameters.AddWithValue("@cityID", ID);
       


            try
            {


                updateCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: " + ex.ToString());
            }


        }


    }

}